package database.dbRelations;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import main.Main;
import objects.Course;
import objects.Task;
import database.dbObjects.IDBObject;
import database.dbObjects.IDBCourse.DBCourse;
import database.dbObjects.IDBTask.DBTask;

public interface IDBRelCourseTask extends IDBObject {
	
	public class DBRelCourseTask extends DBObject {
		
		public final void init() throws Exception {
			
			createTable();
		}
		
		private final void createTable() throws Exception {
			
			String query = "create table RelCourseTask( " +
	        			   "Course_Name varchar(" + DBCourse.maxLengthName + ") not null, " +
	        			   "Task_Name varchar(" + DBTask.maxLengthName + ") not null, " +
	        			   "foreign key (Course_Name) references Course (Name), " +
	        			   "foreign key (Task_Name) references Task (Name), " +
	        			   "primary key (Course_Name, Task_Name) " +
	        			       "on conflict abort);";
			
			executeCreation(query);
		}
		
		public final boolean add(String courseName, String taskName) throws Exception {
			
			checkConsistance(courseName, taskName);
			
			Vector<Object> v = new Vector<Object>(2);
			v.add(0, courseName);
			v.add(1, taskName);
			
			return super.add("RelCourseTask", v);
		}
		
		 public final boolean delete(String courseName, String taskName) throws Exception {
             
             checkConsistance(courseName, taskName);
             
             Vector<Object> keys = new Vector<Object>(2);
             keys.add(0, courseName);
             keys.add(1, taskName);
             
             return super.delete("RelCourseTask", keyColumns, keys);
		 }
		
	/*	public void printRelCourseTask(String sql) {
			// TODO nog aan te passen!!
			try {
				Statement stat = Main.connection.createStatement();
				ResultSet rs = stat.executeQuery(sql);
				
				System.out.println("Vak ID\t\t|\tFile naam");
				System.out.println("---------------------------------------------------------------");
				while (rs.next()) {
					System.out.println(rs.getString("Course_ID")+"\t\t|\t"+rs.getString("File_Path"));
				}
				rs.close();
			} catch(Exception e){
				System.out.println("test-Exception P10!!!");
			}	
		}
	*/	
		
		public final Course getCourse(String taskName) throws Exception {
			// TODO grondig bekijken!!!!!
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from RelCourseTask join Course " +
					"where ((Task_Name = '" + taskName + "') and " +
					"(RelCourseTask.Course_Name = Course.Name));");
			
			if (rs.next()) {
				Course course = new Course(rs.getString("Name"), 
						rs.getString("Description"), rs.getInt("BeginYear"), 
						rs.getInt("Semester"), rs.getString("MoreInformation"));
				rs.close();
				return course;
			}
			rs.close();
			return null;
		}
		
		public final Vector<Task> getTasks(String courseName) throws Exception {
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from RelCourseTask join Task " + 
					"where ((Course_Name = '" + courseName + "') and " +
					"(RelCourseTask.Task_Name = Task.Name));");
			
			Vector<Task> tasks = new Vector<Task>();
			int counter = 0;
			while (rs.next()) {
				tasks.add(counter, new Task(rs.getString("Name"), rs.getString("Description"), 
						rs.getBoolean("Done"), rs.getInt("Grades"), rs.getString("MoreInformation")));
				counter++;
			}
			rs.close();
			return tasks;
		}
		
		private final void checkConsistance(String courseName, String taskName) throws Exception {
			
			course.checkName(courseName);
			task.checkName(taskName);
		}
		
		
		private static final Vector<String> keyColumns = new Vector<String>(2);
		static {
			keyColumns.add(0, "Course_Name");
			keyColumns.add(1, "Task_Name");
		}
		private final DBCourse course = new DBCourse();
		private final DBTask task = new DBTask();
		
	}
	
}
